﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
using System.Diagnostics;
using System.Threading;
using System.IO;
using System.Text.RegularExpressions;

namespace money
{
    public partial class Form1 : Form
    {
        private Dictionary<string, string> student_hash;
        private string FileName;

        /// <summary>  
        /// 读取Excel文件到DataSet中  
        /// </summary>  
        /// <param name="filePath">文件路径</param>  
        /// <returns></returns>  
        public static DataSet ToDataTable(string filePath)
        {
            string connStr = "";
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType)) return null;

            if (fileType == ".xls")
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
            string sql_F = "Select * FROM [{0}A:K]";

            OleDbConnection conn = null;
            OleDbDataAdapter da = null;
            DataTable dtSheetName = null;

            DataSet ds = new DataSet();
            try
            {
                // 初始化连接，并打开  
                conn = new OleDbConnection(connStr);
                conn.Open();

                // 获取数据源的表定义元数据                         
                string SheetName = "";
                dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                // 初始化适配器  
                da = new OleDbDataAdapter();
                for (int i = 0; i < dtSheetName.Rows.Count; i++)
                {
                    SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];

                    if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
                    {
                        continue;
                    }

                    da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
                    DataSet dsItem = new DataSet();
                    da.Fill(dsItem, SheetName);

                    ds.Tables.Add(dsItem.Tables[0].Copy());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("excel文件读取出错,请检查对应的文件");
                return null;
            }
            finally
            {
                // 关闭连接  
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    da.Dispose();
                    conn.Dispose();
                }
            }
            return ds;
        }

        public Form1()
        {
            InitializeComponent();
            Control.CheckForIllegalCrossThreadCalls = false;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            student_hash = new Dictionary<string, string>();
            
            Thread r = new Thread(loadstudentdata);
            r.IsBackground = true;
            r.Start(); //数据初始化线程启动


        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件|*.*";
            ofd.ValidateNames = true;
            ofd.CheckPathExists = true;
            ofd.CheckFileExists = true;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                FileName = ofd.FileName;
            }
            else
            {
                FileName = "";
            }
            Thread r = new Thread(readstudentdata);
            r.IsBackground = true;
            r.Start(); //数据初始化线程启动
        }

        private void listView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        //读取数据线程
        public void loadstudentdata()
        {
            listView1.Enabled = false;
            button1.Enabled = false;
            button2.Enabled = false;
            progressBar1.Value = 0;
            groupBox3.Text = "加载姓名学号数据库.....";
            if (student_hash.Count == 0)
            {
                DataSet student_data = ToDataTable(System.IO.Directory.GetCurrentDirectory() + "\\data\\student.xlsx");
                if (student_data != null)
                {
                    progressBar1.Maximum = student_data.Tables[0].Rows.Count;
                    foreach (DataRow mDr in student_data.Tables[0].Rows)
                    {
                        if (mDr[0].ToString() != "")
                        {
                            try
                            {
                                student_hash.Add(mDr[0].ToString(), mDr[1].ToString());
                            }
                            catch (System.ArgumentException e)
                            {
                                MessageBox.Show("数据库内有重复学号：" + mDr[0].ToString()+"\n请火速删除他。");
                            }
                        }
                        progressBar1.Value++;
                    }
                    student_data.Clear();
                    student_data = null;
                }
                else
                {
                    System.Environment.Exit(0);
                }

            }
            groupBox3.Text = "处理完成";
            progressBar1.Value = 0;
            listView1.Enabled = true;
            button1.Enabled = true;
            button2.Enabled = true;
        }

        //比对数据线程
        public void readstudentdata()
        {
            listView1.Enabled = false;
            button1.Enabled = false;
            button2.Enabled = false;
            progressBar1.Value = 0;
            listView1.Items.Clear();
            groupBox3.Text = "比对中.....";
            if (FileName != "")
            {
                DataSet student_data = ToDataTable(FileName);
                if (student_data != null)
                {
                    progressBar1.Maximum = student_data.Tables[0].Rows.Count;
                    for (int i = 2; i < student_data.Tables[0].Rows.Count; i++)
                    {
                        DataRow mDr = student_data.Tables[0].Rows[i];
                        try
                        {
                            Debug.WriteLine(mDr[1]);
                            if (mDr[1].ToString() == "")
                            {
                                continue;
                            }
                            else if (!IsInt(mDr[0].ToString()))
                            {
                                break;
                            }
                            else if (student_hash[mDr[1].ToString()] != mDr[2].ToString())
                            {
                                ListViewItem lvi = new ListViewItem(mDr[1].ToString());
                                lvi.SubItems.Add(mDr[2].ToString());
                                lvi.SubItems.Add(student_hash[mDr[1].ToString()]);
                                this.listView1.Items.Add(lvi);
                            }
                        }
                        catch (System.Collections.Generic.KeyNotFoundException e)
                        {
                            ListViewItem lvi = new ListViewItem(mDr[1].ToString());
                            lvi.SubItems.Add(mDr[2].ToString());
                            lvi.SubItems.Add("该学号不在数据库里");
                            this.listView1.Items.Add(lvi);
                        }
                        progressBar1.Value++;
                    }
                    student_data.Clear();
                    student_data = null;
                    groupBox3.Text = "处理完成";
                }
                else
                {
                    groupBox3.Text = "EXCEL文件读取出错";
                }

            }
            else
            {
                groupBox3.Text = "未选择文件";
            }
            progressBar1.Value = 0;
            listView1.Enabled = true;
            button1.Enabled = true;
            button2.Enabled = true;
        }


        private void button2_Click(object sender, EventArgs e)
        {
            if (listView1.Items.Count == 0)
            {
                MessageBox.Show("错误列表内无数据，无法导出");
            }
            else
            {
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "excel文件(*.xlsx)|*.xlsx";
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    File.Copy(System.IO.Directory.GetCurrentDirectory() + "\\data\\student_error.xlsx", sfd.FileName, true);
                    string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + sfd.FileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0\"";
                    using (OleDbConnection ole_conn = new OleDbConnection(strCon))
                    {
                        ole_conn.Open();
                        foreach (ListViewItem lvi in listView1.Items)
                        {
                            try
                            {
                                using (OleDbCommand ole_cmd = ole_conn.CreateCommand())
                                {
                                    ole_cmd.CommandText = "insert into [Sheet1$](学号,姓名,真实姓名)values('" + lvi.Text + "','" + lvi.SubItems[1].Text + "','" + lvi.SubItems[2].Text + "')";
                                    Debug.WriteLine(ole_cmd.CommandText);
                                    ole_cmd.ExecuteNonQuery();
                                }
                            }
                            catch
                            {
                                ole_conn.Close();
                                File.Delete(sfd.FileName);
                                MessageBox.Show("导出失败");
                                break;
                            }
                        }
                        ole_conn.Close();
                    }
                    MessageBox.Show("导出成功");
                }
            }
        }
        public static bool IsInt(string value)
        {
            return Regex.IsMatch(value, @"^[+-]?\d*$");
        }
    }
}
